﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Car;
using System.Data;

namespace ServiceApplication.Dao.Car
{
    public class CarDao
    {
        public int AddEntity(CarEntity carEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@car_id", carEntity.car_id));
            parameterList.Add(new SqlParameter("@car_number", carEntity.car_number));
            parameterList.Add(new SqlParameter("@car_name", carEntity.car_name));
            parameterList.Add(new SqlParameter("@car_descn", carEntity.car_descn));


            string sql = "insert into car_car (car_number,car_name,car_descn) values " +
                " (@car_number,@car_name,@car_descn); " +
                " select @@identity";
            string dept_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(dept_id);
        }

        public void DeleteEntity(int car_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@car_id", car_id));
            string sql = "delete from car_car where car_id=@car_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public CarEntity QueryEntity(int car_id)
        {
            CarEntity carEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@car_id", car_id));
            string sql = "select car_id,car_number,car_name,car_descn " +
                " from car_car where car_id=@car_id ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            carEntity = new CarEntity();
            carEntity.car_id = int.Parse(result[0][0]);
            carEntity.car_number = result[0][1];
            carEntity.car_name = result[0][2];
            carEntity.car_descn = result[0][3];
            return carEntity;
        }

        public DataTable Query(CarEntity carEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();

            string sql = "select car_id 车辆编码,car_number 车辆牌号,car_name 车辆名称,car_descn 车辆备注 " +
                             " from car_car where car_id=car_id ";
            if (!string.IsNullOrEmpty(carEntity.car_number))
            {
                parameterList.Add(new SqlParameter("@car_number", carEntity.car_number));
                sql += " and car_number=@car_number ";
            }
            if (!string.IsNullOrEmpty(carEntity.car_name))
            {
                parameterList.Add(new SqlParameter("@car_name", carEntity.car_name));
                sql += " and car_name=@car_name ";
            }

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryAll()
        {
            string sql = " select car_id 车辆编码,car_number 车辆牌号,car_name 车辆名称,car_descn 车辆备注 " +
                             " from car_car  ";

            return DBExecuteUtil.querySqlTable(sql);
        }

        public void UpdateEntity(CarEntity carEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@car_id", carEntity.car_id));
            parameterList.Add(new SqlParameter("@car_number", carEntity.car_number));
            parameterList.Add(new SqlParameter("@car_name", carEntity.car_name));
            parameterList.Add(new SqlParameter("@car_descn", carEntity.car_descn));

            string sql = "update car_car set car_number=@car_number,car_name=@car_name,car_descn=@car_descn where car_id=@car_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }
    }
}
